﻿CREATE FUNCTION [dbo].[fs_Loans_NS]
	(
		@LoanID int = null,
		@StateID int = null,
		@Access int = 1
	)
RETURNS @IDs TABLE (LoanID int)
AS
BEGIN

IF @StateID IS NULL SELECT @StateID = StatusID FROM t_Loan WHERE ID = @LoanID

DECLARE @NSID int SET @NSID = dbo.UserNameSpaceID()

DECLARE @NSs TABLE(NameSpaceID int)
INSERT INTO @NSs SELECT NameSpaceID FROM t_NameSpaces WHERE ParentID = @NSID

DECLARE @StateIDs TABLE(StateID int)
INSERT INTO @StateIDs SELECT ID FROM fs_StateID(@StateID,@Access)

DECLARE @ContractHolderIDs TABLE(CHID int)
INSERT INTO @ContractHolderIDs SELECT CHID FROM fs_ContractHolders()

IF @LoanID IS NULL
	INSERT INTO @IDs
	SELECT l.ID FROM t_Loan l
	INNER JOIN @StateIDs S	ON l.StatusID = S.StateID
	INNER JOIN @NSs nss	ON l.NameSpaceID = nss.NameSpaceID
	INNER JOIN @ContractHolderIDs CHIDs ON l.ContractHolderID = CHIDs.CHID
ELSE
	INSERT INTO @IDs
	SELECT l.ID FROM t_Loan l
	INNER JOIN @StateIDs S ON l.StatusID = S.StateID
	INNER JOIN @NSs nss	ON l.NameSpaceID = nss.NameSpaceID
	INNER JOIN @ContractHolderIDs CHIDs ON l.ContractHolderID = CHIDs.CHID
	WHERE (l.ID = @LoanID)

RETURN

END




